/** These top lines before the CREATE statement will not run
	unless the database exists. For first-time creation, comment
	out the first six statements.
 **/
USE NETSYS
ALTER DATABASE NETSYS SET SINGLE_USER WITH ROLLBACK IMMEDIATE
USE master
Go
DROP DATABASE NETSYS
Go

CREATE DATABASE NETSYS
Go

USE NETSYS
Go

CREATE TABLE QUIZ(
	QUIZ_ID				integer PRIMARY KEY IDENTITY,
	QUIZ_NAME			varchar(50) NOT NULL,
	ALLOW_BACKTRACK		integer NULL DEFAULT 0
	/* Make sure the ALLOW_BACKTRACK column only takes a 1 or 0
	   (true or false, respectively)
	*/
	CONSTRAINT ALLOW_BACKTRACK_CONSTRAINT
		CHECK (ALLOW_BACKTRACK=1 OR ALLOW_BACKTRACK=0)
)

CREATE TABLE QUESTION(
	QUESTION_ID			integer PRIMARY KEY IDENTITY,
	QUESTION_TEXT		varchar(1024) NOT NULL,
	CATEGORY_ID			integer NOT NULL,
	LEVEL_ID			integer NOT NULL,
	/* 'M' for multiple choice or 'S' for short answer. */
	QUESTION_TYPE		char(1) NOT NULL DEFAULT 'S'
	CONSTRAINT QUESTION_TYPE_CONSTRAINT
		CHECK (QUESTION_TYPE='S' OR QUESTION_TYPE='M')
)

CREATE TABLE QUIZ_QUESTION(
	QUIZ_ID				integer NOT NULL,
	QUESTION_ID			integer NOT NULL,
	QUESTION_PRIORITY	integer NULL DEFAULT 0
)

ALTER TABLE QUIZ_QUESTION
	ADD CONSTRAINT PK_QUIZ_QUESTION
	PRIMARY KEY CLUSTERED(QUIZ_ID, QUESTION_ID)

CREATE TABLE APPLICANT(
	APPLICANT_ID		integer PRIMARY KEY IDENTITY,
	POSITION_ID			integer NOT NULL,
	FIRST_NAME			varchar(20) NOT NULL,
	LAST_NAME			varchar(40) NOT NULL,
	PHONE_NUMBER		varchar(12) NULL,
	EMAIL				varchar(50) NOT NULL,
	INTERVIEW_DATE		datetime NULL,
	APPLICANT_KEY		varchar(40) NOT NULL
)
CREATE TABLE POSITION(
	POSITION_ID			integer PRIMARY KEY IDENTITY,
	QUIZ_ID				integer NOT NULL,
	POSITION_TITLE		varchar(40) NOT NULL,
	HIRE_DATE			datetime NULL
)
CREATE TABLE QUIZ_ATTEMPT(
	ATTEMPT_ID			integer PRIMARY KEY IDENTITY,
	QUIZ_ID				integer NOT NULL,
	APPLICANT_ID		integer NOT NULL,
	ATTEMPT_DATE		datetime NULL,
	GRADED				integer NOT NULL DEFAULT 0
	CONSTRAINT ALLOW_GRADED
		CHECK (GRADED=1 OR GRADED=0)
)

CREATE TABLE ANSWER(
	ANSWER_ID				integer PRIMARY KEY IDENTITY,
	QUESTION_ID				integer NOT NULL,
	ANSWER_TEXT				varchar(500) NOT NULL,
	IS_CORRECT_ANSWER		integer NOT NULL DEFAULT 0
	CONSTRAINT ALLOW_IS_CORRECT_ANSWER
		CHECK (IS_CORRECT_ANSWER=1 OR IS_CORRECT_ANSWER=0),
	ANSWER_PRIORITY			integer NULL DEFAULT 0
	
)
CREATE TABLE QUESTION_LEVEL(
	LEVEL_ID			integer PRIMARY KEY IDENTITY,
	LEVEL_DESCRIPTION   varchar(30) NOT NULL,
	LEVEL_WEIGHT		decimal NULL DEFAULT 0.0
)
 
CREATE TABLE QUESTION_RESULT(
	ATTEMPT_ID			integer NOT NULL,
	QUESTION_ID			integer NOT NULL,
	ANSWERED_CORRECTLY	integer	NULL
	/* Default to -1 for not graded */
	CONSTRAINT ALLOW_ANSWERED_CORRECTLY DEFAULT -1
		CHECK (ANSWERED_CORRECTLY=1 OR ANSWERED_CORRECTLY=0 OR ANSWERED_CORRECTLY=-1),
)

ALTER TABLE QUESTION_RESULT
	ADD CONSTRAINT PK_QUESTION_RESULT
	PRIMARY KEY CLUSTERED(ATTEMPT_ID, QUESTION_ID)

CREATE TABLE QUESTION_CATEGORY(
	CATEGORY_ID			integer PRIMARY KEY IDENTITY,
	CATEGORY_NAME		varchar(40) NOT NULL,
)

CREATE TABLE SHORT_ANSWER(
	SHORT_ANSWER_ID		integer PRIMARY KEY IDENTITY,
	QUESTION_ID			integer NOT NULL,
	ATTEMPT_ID			integer NOT NULL,
	ANSWER_TEXT			varchar(4096) NOT NULL
)

/* Foreign Key Constraints */

ALTER TABLE QUESTION_RESULT
	ADD CONSTRAINT QUESTION_RESULT_QUESTION_ID
	FOREIGN KEY(QUESTION_ID)
	REFERENCES QUESTION(QUESTION_ID)

ALTER TABLE APPLICANT
	ADD CONSTRAINT APPLICANT_POSITION_ID
	FOREIGN KEY(POSITION_ID)
	REFERENCES POSITION(POSITION_ID)

ALTER TABLE POSITION
	ADD CONSTRAINT POSITION_QUIZ_ID
	FOREIGN KEY(QUIZ_ID)
	REFERENCES QUIZ(QUIZ_ID)

ALTER TABLE QUIZ_ATTEMPT
	ADD CONSTRAINT QUIZ_ATTEMPT_APPLICANT
	FOREIGN KEY(APPLICANT_ID)
	REFERENCES APPLICANT(APPLICANT_ID)
	ON UPDATE CASCADE
	ON DELETE CASCADE

ALTER TABLE QUIZ_ATTEMPT
	ADD CONSTRAINT QUIZ_ATTEMPT_QUIZ_ID
	FOREIGN KEY(QUIZ_ID)
	REFERENCES QUIZ(QUIZ_ID)

ALTER TABLE QUESTION
	ADD CONSTRAINT QUESTION_QUESTION_LEVEL
	FOREIGN KEY(LEVEL_ID)
	REFERENCES QUESTION_LEVEL(LEVEL_ID)
	ON UPDATE CASCADE

ALTER TABLE SHORT_ANSWER
	ADD CONSTRAINT SHORT_ANSWER_QUESTION_ID
	FOREIGN KEY(QUESTION_ID)
	REFERENCES QUESTION(QUESTION_ID)
	ON UPDATE CASCADE

ALTER TABLE SHORT_ANSWER
	ADD CONSTRAINT SHORT_ANSWER_ATTEMPT_ID
	FOREIGN KEY(ATTEMPT_ID)
	REFERENCES QUIZ_ATTEMPT(ATTEMPT_ID)
	ON UPDATE CASCADE
	ON DELETE CASCADE

ALTER TABLE QUESTION
	ADD	CONSTRAINT QUESTION_QUESTION_CATEGORY
	FOREIGN KEY(CATEGORY_ID)
	REFERENCES QUESTION_CATEGORY(CATEGORY_ID)
	ON UPDATE CASCADE

ALTER TABLE QUIZ_QUESTION
	ADD CONSTRAINT QUIZ_QUESTION_QUIZ
	FOREIGN KEY(QUIZ_ID)
	REFERENCES QUIZ(QUIZ_ID)
	ON UPDATE CASCADE
	ON DELETE CASCADE

ALTER TABLE QUIZ_QUESTION
	ADD CONSTRAINT QUIZ_QUESTION_QUESTION 
	FOREIGN KEY(QUESTION_ID) 
	REFERENCES QUESTION(QUESTION_ID)
	ON UPDATE CASCADE
	ON DELETE CASCADE

ALTER TABLE QUESTION_RESULT
	ADD CONSTRAINT QUESTION_RESULT_QUIZ_ATTEMPT 
	FOREIGN KEY(ATTEMPT_ID) 
	REFERENCES QUIZ_ATTEMPT(ATTEMPT_ID)
	ON UPDATE CASCADE
	ON DELETE CASCADE

ALTER TABLE APPLICANT
    ADD CONSTRAINT APPLICANT_POSITION
	FOREIGN KEY(POSITION_ID)
	REFERENCES POSITION(POSITION_ID)
	ON UPDATE CASCADE
	ON DELETE CASCADE

ALTER TABLE ANSWER
	ADD CONSTRAINT ANSWER_QUESTION_ID
	FOREIGN KEY(QUESTION_ID)
	REFERENCES QUESTION(QUESTION_ID)
	ON UPDATE CASCADE
	ON DELETE CASCADE
